#!/usr/bin/env bash

dt="`date -d "$d  33 days ago "  "+%Y-%m"`"
mysql -uroot -proot -hhadoop2<<EOF
use oraclecd;
select A.MONTH,
SUM(CASE WHEN A.COUNT<'1' THEN 1 ELSE 0 END) AS zero,
SUM(CASE WHEN A.COUNT='1' THEN 1 ELSE 0 END) AS one,
SUM(CASE WHEN A.COUNT='2' THEN 1 ELSE 0 END) AS two,
SUM(CASE WHEN A.COUNT='3' THEN 1 ELSE 0 END) AS three,
SUM(CASE WHEN A.COUNT='4' THEN 1 ELSE 0 END) AS four,
SUM(CASE WHEN A.COUNT='5' THEN 1 ELSE 0 END) AS five,
SUM(CASE WHEN A.COUNT>'5' THEN 1 ELSE 0 END) AS overfive
from (SELECT SUBSTR(SENDTIME,1,7)AS MONTH,CARDUSERID,COUNT(CARDUSERID)AS COUNT
      FROM TBL_ORDERDETAILS  WHERE ORDER_DEAL = 4 and SUBSTR(SENDTIME,1,7)>='${dt}'
 GROUP BY CARDUSERID,SUBSTR(SENDTIME,1,7))A
GROUP BY MONTH;
EOF